package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.Event;
import pos.domain.EventImpl;
import util.UIDGenerator;

public class JDBCEventDAO implements IEventDAO{

	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCEventDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	public List<Event> getAllEvents() {
		
		String sql = "SELECT * FROM event";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Event> listEvents = null;

		try {
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();
			listEvents = new ArrayList<Event>();

			while (result.next()) {
				Event event = new EventImpl();
				event.setId(result.getString("idevent"));
				event.setTitle(result.getString("title"));
				event.setDescription(result.getString("description"));
				event.setCategory(new JDBCCategoryDAO().getCategoryById(result.getString("idcategory")));
				event.setDate(result.getDate("date"));
				event.setUrlEvent(result.getString("url"));
				event.setOriginalText(result.getString("originaltext"));
				event.setTranslatedText(result.getString("translatedtext"));
				event.setDiscipline(new JDBCDisciplineDAO().getDisciplineById(result.getString("iddiscipline")));
				event.setListEntities(new JDBCEntityDAO().getListEntitiesByEvent(result.getString("idevent")));
				event.setListRelations(new JDBCRelationDAO().getListRelationsByEvent(result.getString("idevent")));

				listEvents.add(event);
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					conn.close();
				}
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}
		}

		return listEvents;
	}

	public Event getEventById(String idEvent) {

		Event event = new EventImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Events with that ID.
		String sql = "SELECT * FROM event WHERE (idevent = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvent);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				event.setId(result.getString("idevent"));
				event.setTitle(result.getString("title"));
				event.setDescription(result.getString("description"));
				event.setCategory(new JDBCCategoryDAO().getCategoryById(result.getString("idcategory")));
				event.setDate(result.getDate("date"));
				event.setUrlEvent(result.getString("url"));
				event.setOriginalText(result.getString("originaltext"));
				event.setTranslatedText(result.getString("translatedtext"));
				event.setDiscipline(new JDBCDisciplineDAO().getDisciplineById(result.getString("iddiscipline")));
				event.setListEntities(new JDBCEntityDAO().getListEntitiesByEvent(idEvent));
				event.setListRelations(new JDBCRelationDAO().getListRelationsByEvent(idEvent));
				
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return event;
	}

	public Event getEventByTitle(String title) {

		Event event = new EventImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the Events with that title.
		String sql = "SELECT * FROM event WHERE (title = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, title);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				event.setId(result.getString("idevent"));
				event.setTitle(result.getString("title"));
				event.setDescription(result.getString("description"));
				event.setCategory(new JDBCCategoryDAO().getCategoryById(result.getString("idcategory")));
				event.setDate(result.getDate("date"));
				event.setUrlEvent(result.getString("url"));
				event.setOriginalText(result.getString("originaltext"));
				event.setTranslatedText(result.getString("translatedtext"));
				event.setDiscipline(new JDBCDisciplineDAO().getDisciplineById(result.getString("iddiscipline")));
				event.setListEntities(new JDBCEntityDAO().getListEntitiesByEvent(result.getString("idevent")));
				event.setListRelations(new JDBCRelationDAO().getListRelationsByEvent(result.getString("idevent")));
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return event;
	}

	public Event insertEvent(Event event, String idCategory, String idDiscipline) {

		PreparedStatement stmt = null;
		String idevent = "";
		
		if(event.getId()==null){
			idevent = UIDGenerator.getInstance().getKey(); // ID for DB
			event.setId(idevent);
		}
		idevent = event.getId();
		

		String sql = "INSERT INTO event (idevent,title,description,idcategory,date,url,originaltext,translatedtext,iddiscipline) VALUES (?,?,?,?,?,?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, idevent);
			stmt.setString(2, event.getTitle());
			stmt.setString(3, event.getDescription());
			stmt.setString(4, idCategory);
			stmt.setDate(5, event.getDate());
			stmt.setString(6, event.getUrlEvent());
			stmt.setString(7, event.getOriginalText());
			stmt.setString(8, event.getTranslatedText());
			stmt.setString(9, idDiscipline);

			stmt.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();

		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return event;		
	}

	public void removeEvent(Event event) {

		PreparedStatement stmt = null;
		
		String sql = "DELETE FROM event WHERE (idevent = ?)";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, event.getId());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (Exception e) {

			}
		}
	}

	public boolean existEvent(String title) {

		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM event WHERE (title = ?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, title);
			result = stmt.executeQuery();

			if (result.next()==false) {
				res = false;
			} else {
				res = true;
		}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;

	}

}
